package week05.question10.part02;

import week05.question10.DBConnectionUtils;
import week05.question10.dao.UserDAO;
import week05.question10.entity.UserEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.stream.IntStream;

public class TxJDBCUserDaoImpl implements UserDAO {

    @Override
    public void insert(UserEntity user) {
        String sql = " insert into ms_user(admin_id, loginname, realname, email) values(?,?,?,? )";
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        try {
            dbConnection = DBConnectionUtils.getDBConnection();
            dbConnection.setAutoCommit(false);

            preparedStatement = dbConnection.prepareStatement(sql);
            //be careful! parameterIndex is beginning with 1;
            preparedStatement.setString(1, user.getAdminId());
            preparedStatement.setString(2, user.getLoginName());
            preparedStatement.setString(3, user.getRealName());
            preparedStatement.setString(4, user.getEmail());
            int i = preparedStatement.executeUpdate();
            //be careful! must commit transition
            dbConnection.commit();
            System.out.println("insert success, done "+i);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DBConnectionUtils.close(dbConnection, preparedStatement);
        }
    }

    @Override
    public void batchSave(List<UserEntity> users) {
        String sql = " insert into ms_user(admin_id, loginname, realname, email) values(?,?,?,? )";
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        try {
            dbConnection = DBConnectionUtils.getDBConnection();
            dbConnection.setAutoCommit(false);

            preparedStatement = dbConnection.prepareStatement(sql);
            for (UserEntity user : users) {
                //be careful! parameterIndex is beginning with 1;
                preparedStatement.setString(1, user.getAdminId());
                preparedStatement.setString(2, user.getLoginName());
                preparedStatement.setString(3, user.getRealName());
                preparedStatement.setString(4, user.getEmail());
                preparedStatement.addBatch();
            }
            int[] ints = preparedStatement.executeBatch();
            //be careful! must commit transition
            dbConnection.commit();
            System.out.println("batchSave success, done "+ IntStream.of(ints).sum());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DBConnectionUtils.close(dbConnection, preparedStatement);
        }
    }

    @Override
    public void delete(String adminId) {
        String sql = "delete from ms_user where admin_id=?";
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        try {
            dbConnection = DBConnectionUtils.getDBConnection();
            dbConnection.setAutoCommit(false);

            preparedStatement = dbConnection.prepareStatement(sql);
            //be careful! parameterIndex is beginning with 1;
            preparedStatement.setString(1, adminId);
            int ints = preparedStatement.executeUpdate();
            //be careful! must commit transition
            dbConnection.commit();
            System.out.println("delete success, done "+ ints);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DBConnectionUtils.close(dbConnection, preparedStatement);
        }
    }

    @Override
    public void update(UserEntity user) {
        String sql = "update ms_user set loginname=?, realname=?, email=? where admin_id=?";
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        try {
            dbConnection = DBConnectionUtils.getDBConnection();
            dbConnection.setAutoCommit(false);

            preparedStatement = dbConnection.prepareStatement(sql);
            //be careful! parameterIndex is beginning with 1;
            preparedStatement.setString(1, user.getLoginName());
            preparedStatement.setString(2, user.getRealName());
            preparedStatement.setString(3, user.getEmail());
            preparedStatement.setString(4, user.getAdminId());

            int ints = preparedStatement.executeUpdate();
            //be careful! must commit transition
            dbConnection.commit();
            System.out.println("update success, done "+ ints);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DBConnectionUtils.close(dbConnection, preparedStatement);
        }
    }

    @Override
    public List<UserEntity> query(String adminId) {
        String sql = "select * from ms_user where admin_id=?";
        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;
        try {
            dbConnection = DBConnectionUtils.getDBConnection();
            dbConnection.setAutoCommit(false);

            preparedStatement = dbConnection.prepareStatement(sql);
            //be careful! parameterIndex is beginning with 1;
            preparedStatement.setString(1, adminId);

            ResultSet resultSet = preparedStatement.executeQuery();
            List<UserEntity> resultList = new ArrayList<>();
            //admin_id, loginname, realname, email
            while(resultSet.next()){
                String admin_id = resultSet.getString("admin_id");
                String loginname = resultSet.getString("loginname");
                String realname = resultSet.getString("realname");
                String email = resultSet.getString("email");
                resultList.add(new UserEntity(adminId, loginname, realname, email));
            }
            //be careful! must commit transition
            dbConnection.commit();
            System.out.println("query success, done "+ resultList.size());
            return resultList;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                dbConnection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            DBConnectionUtils.close(dbConnection, preparedStatement);
        }
        return Collections.emptyList();
    }
}
